package com.nl.sevenuser;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.dbutils.DbUtils;
import org.apache.log4j.Logger;

import com.nl.redis.RedisCluster;
import com.nl.util.ConfigUtils;
import com.nl.util.ConnectionManager;

import js.App;

public class LoadSevenUser extends Thread {
	private String cityId;
	private int count1=0;
	private int count2=0;
	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	public LoadSevenUser() {

	}
	public LoadSevenUser(String cityid) {
		this.cityId=cityid;
	}
	private static final Logger logger = Logger.getLogger(LoadSevenUser.class);
	public static final String REDISIP;
	private static final String TABLE;
	static{		
		REDISIP = ConfigUtils.getInstance().getValue("redisIp");
		TABLE = ConfigUtils.getInstance().getValue("table");
	}
	@Override
	public void run(){	
		logger.info("*********线程启动***********");
		loadSevenUserIsLocal(cityId);
		loadSevenUserIsNotLocal(cityId);
		int count = count1+count2;
		App.count1 +=count; 
		logger.info("此线程已加载了"+count);		
	}
	/**
	 * 加载七日已发表本地用户
	 */
	private void loadSevenUserIsLocal(String cityid) {
		logger.info("*********开始加载***********");

		long start = System.currentTimeMillis();
		Connection conn = null;
		ResultSet rs = null;
		//		PreparedStatement pres = null;
		Statement stmt = null;
		RedisCluster redisCluster = null;
		try {
			redisCluster = new RedisCluster(REDISIP);
		} catch (IOException e1) {
			e1.printStackTrace();
		}	
		String sql="select t1.phone_number,t1.arrival_city,to_char(send_time, 'yyyy-mm-dd hh24:mi:ss') send_time,t1.region_id,t1.mgr_id,t1.is_local,(t2.local_date-(sysdate-send_time))*24*60*60  as secends"+
				" from "+TABLE+" t1 join sms_template_attr t2 "+
				" on t1.mgr_id = t2.id where t1.is_local=0 and mgr_id in ("+cityId+")";
		if ("1".equals(App.FLAG)) {
			sql = sql + " and t1." +App.DATE_SQL; 
			logger.info(sql);
		}
		try {			
			conn = ConnectionManager.getInstance().getConnection();						
			//			pres = conn.prepareStatement(sql);
			//			pres.setString(1, cityid);
			//			pres.setFetchSize(1000);
			//			rs = pres.executeQuery();	
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_READ_ONLY);
			stmt.setFetchSize(300);
			rs = stmt.executeQuery(sql);

			while(rs.next()){				
				if (null ==rs.getString("ARRIVAL_CITY") || "".equals(rs.getString("ARRIVAL_CITY"))
						|| null ==rs.getString("SEND_TIME") || "".equals(rs.getString("SEND_TIME"))
						|| null ==rs.getString("REGION_ID") || "".equals(rs.getString("REGION_ID"))
						|| null ==rs.getString("IS_LOCAL") || "".equals(rs.getString("IS_LOCAL"))
						|| null ==rs.getString("PHONE_NUMBER") || "".equals(rs.getString("PHONE_NUMBER"))
						|| null ==rs.getString("MGR_ID") || "".equals(rs.getString("MGR_ID"))) {
					continue;
				}
				Map<String, String> hash =new HashMap<String, String>();				
				hash.put("ARRIVAL_CITY", rs.getString("ARRIVAL_CITY"));
				hash.put("SEND_TIME", rs.getString("SEND_TIME"));
				hash.put("REGION_ID", rs.getString("REGION_ID"));				
				hash.put("IS_LOCAL", rs.getString("IS_LOCAL"));
				String key =rs.getString("PHONE_NUMBER")+"#"+rs.getString("MGR_ID");
				String sevenSendTime = rs.getString("SEND_TIME");
				int seconds = rs.getInt("secends");
				if (null!= App.MGR_IDS && !"".equals(App.MGR_IDS)) {
					if (Arrays.asList(App.MGR_IDS.split(",")).contains(rs.getString("MGR_ID"))) {
						redisCluster.expire(key,0);
						continue;
					}
				}
				if (seconds>0) {
					Map<String, String> map  = redisCluster.hgetAll(key);
					if (null == map || map.isEmpty()) {
						count1++;
						logger.info("sevenDate:"+rs.getString("SEND_TIME")+";seconds:"+seconds);
						redisCluster.hmset(key, hash);
						redisCluster.expire(key, seconds);
					} else {
						String redisSendTime = map.get("SEND_TIME");
						Date redisDate = sdf.parse(redisSendTime);
						Date sevenDate = sdf.parse(sevenSendTime);
						if ((sevenDate.getTime() - redisDate.getTime()) >60000) {
							logger.info("redisSendTime:"+map.get("SEND_TIME") + "sevenDate:"+rs.getString("SEND_TIME")+seconds);
							count1++;
							redisCluster.hmset(key, hash);
							redisCluster.expire(key, seconds);
						}
					}
				}
				if (count1%5000==0 && count1!=0) {
					long end = System.currentTimeMillis();
					logger.info("本地用户已经加载了"+count1+",耗时:"+(end-start));
				}				
			}
			logger.info("本地用户共加载"+count1);

		} catch (Exception e) {
			logger.info("********加载七日表错误**********");
			e.printStackTrace();
		} finally {
			try {
				if (null != rs) {
					rs.close();
					rs = null;
				}
				if (null != stmt) {
					stmt.close();
				}
			} catch (SQLException e) {

				e.printStackTrace();
			}
			DbUtils.closeQuietly(conn, null, null);
			try {
				redisCluster.close();
			} catch (IOException e) {

				e.printStackTrace();
			}
		}	
	}

	/**
	 * 加载七日已发表外地用户
	 */
	private void loadSevenUserIsNotLocal(String city_id) {
		logger.info("*********开始加载***********");
		long start = System.currentTimeMillis();
		Connection conn = null;
		ResultSet rs = null;
		//		PreparedStatement pres = null;
		Statement stmt = null;
		RedisCluster redisCluster = null;
		try {
			redisCluster = new RedisCluster(REDISIP);
		} catch (IOException e1) {
			e1.printStackTrace();
		}	;
		String sql="select t1.phone_number,t1.arrival_city,to_char(send_time, 'yyyy-mm-dd hh24:mi:ss') send_time,t1.region_id,t1.mgr_id,t1.is_local,(t2.nonlocal_date-(sysdate-send_time))*24*60*60  as secends"+
				" from "+TABLE+" t1 join sms_template_attr t2"+
				"  on t1.mgr_id = t2.id where t1.is_local=1  and mgr_id in ("+cityId+")";
		if ("1".equals(App.FLAG)) {
			sql = sql + " and t1." +App.DATE_SQL; 
			logger.info(sql);
		}
		try {			
			conn = ConnectionManager.getInstance().getConnection();						
			//			pres = conn.prepareStatement(sql);
			//			pres.setString(1, city_id);
			//			pres.setFetchSize(1000);
			//			rs = pres.executeQuery();
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_READ_ONLY);
			stmt.setFetchSize(300);
			rs = stmt.executeQuery(sql);

			while(rs.next()){				
				
				if (null ==rs.getString("ARRIVAL_CITY") || "".equals(rs.getString("ARRIVAL_CITY"))
						|| null ==rs.getString("SEND_TIME") || "".equals(rs.getString("SEND_TIME"))
						|| null ==rs.getString("REGION_ID") || "".equals(rs.getString("REGION_ID"))
						|| null ==rs.getString("IS_LOCAL") || "".equals(rs.getString("IS_LOCAL"))
						|| null ==rs.getString("PHONE_NUMBER") || "".equals(rs.getString("PHONE_NUMBER"))
						|| null ==rs.getString("MGR_ID") || "".equals(rs.getString("MGR_ID"))) {
					continue;
				}
				Map<String, String> hash =new HashMap<String, String>();				
				hash.put("ARRIVAL_CITY", rs.getString("ARRIVAL_CITY"));
				hash.put("SEND_TIME", rs.getString("SEND_TIME"));
				hash.put("REGION_ID", rs.getString("REGION_ID"));				
				hash.put("IS_LOCAL", rs.getString("IS_LOCAL"));
				String key =rs.getString("PHONE_NUMBER")+"#"+rs.getString("MGR_ID");
				String sevenSendTime = rs.getString("SEND_TIME");
				int seconds = rs.getInt("secends");
				if (null!= App.MGR_IDS && !"".equals(App.MGR_IDS)) {
					if (Arrays.asList(App.MGR_IDS.split(",")).contains(rs.getString("MGR_ID"))) {
						redisCluster.expire(key,0);
						continue;
					}
				}
				if (seconds>0) {
					Map<String, String> map  = redisCluster.hgetAll(key);
					if (null == map || map.isEmpty()) {
						count2++;
						logger.info("sevenDate:"+rs.getString("SEND_TIME")+";seconds:"+seconds);
						redisCluster.hmset(key, hash);
						redisCluster.expire(key, seconds);
					} else {
						String redisSendTime = map.get("SEND_TIME");
						Date redisDate = sdf.parse(redisSendTime);
						Date sevenDate = sdf.parse(sevenSendTime);
						if ((sevenDate.getTime() - redisDate.getTime()) >60000) {
							logger.info("redisSendTime:"+map.get("SEND_TIME") + "sevenDate:"+rs.getString("SEND_TIME")+seconds);
							count2++;
							redisCluster.hmset(key, hash);
							redisCluster.expire(key, seconds);
						}
					}
				}
				if (count2%5000==0 && count2!=0) {
					long end = System.currentTimeMillis();
					logger.info("外地用户已经加载了"+count2+",耗时:"+(end-start));
				}				
			}
			logger.info("外地用户共加载"+count2);
		} catch (Exception e) {
			logger.info("********加载七日表错误**********");
			e.printStackTrace();
		} finally {
			try {
				if (null != rs) {
					rs.close();
					rs = null;
				}
				if (null != stmt) {
					stmt.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DbUtils.closeQuietly(conn, null, null);
			try {
				redisCluster.close();
			} catch (IOException e) {

				e.printStackTrace();
			}
		}	
	}


}
